package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Carimg;
import com.edu.bean.Mycar;
import com.edu.dao.BaseDao;
import com.edu.dao.MycarDao;

public class MycarDaoImpl implements MycarDao {

	@Override
	public List<Mycar> selectCarimgAll(int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Mycar> mycar = new ArrayList();
		String sql = "SELECT * FROM mycar LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				Mycar car = new Mycar();
				car.setMyCarId(res.getInt("myCarId"));
				car.setMyCarName(res.getString("myCarName"));
				car.setMyCarNum(res.getString("myCarNum"));
				car.setMyCarMileage(res.getString("myCarMileage"));
				car.setMyCarDate(res.getDate("myCarDate"));
				car.setMyCarMaint(res.getString("myCarMaint"));
				car.setUserId(res.getInt("userId"));
				car.setCarId(res.getInt("carId"));
				car.setMyCarImg(res.getString("myCarImg"));
				mycar.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return mycar;
	}

	@Override
	public int selectMycartotal(Mycar mycar) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM mycar WHERE 1=1 ";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int deleteMycarById(int mycarid) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM mycar WHERE myCarId = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, mycarid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int updateMycar(Mycar mycar) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE mycar set "
				+ "myCarName = ?,myCarNum = ?,myCarMileage = ?,myCarDate = ?,myCarMaint= ?,userId= ?,carId= ?";
				if(mycar.getMyCarImg()!=null) {
					sql +=",myCarImg= ?";
				}
				sql+= " WHERE myCarId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, mycar.getMyCarName());
			ps.setString(2, mycar.getMyCarNum());
			ps.setString(3, mycar.getMyCarMileage());
			ps.setDate(4, new Date(mycar.getMyCarDate().getTime()));
			ps.setString(5, mycar.getMyCarMaint());
			ps.setInt(6, mycar.getUserId());
			ps.setInt(7, mycar.getCarId());
			if(mycar.getMyCarImg()!=null) {
				ps.setString(8, mycar.getMyCarImg());
				ps.setInt(9, mycar.getMyCarId());
			}else {
				ps.setInt(8, mycar.getMyCarId());
			}
			
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public Mycar selectMycarById(int mycarid) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Mycar car=null;
		String sql = "SELECT * FROM mycar WHERE myCarId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,mycarid);
			res = ps.executeQuery();
			System.out.println(ps);
			if(res.next()) {
				car = new Mycar();
				car.setMyCarId(res.getInt("myCarId"));
				car.setMyCarName(res.getString("myCarName"));
				car.setMyCarNum(res.getString("myCarNum"));
				car.setMyCarMileage(res.getString("myCarMileage"));
				car.setMyCarDate(res.getDate("myCarDate"));
				car.setMyCarMaint(res.getString("myCarMaint"));
				car.setUserId(res.getInt("userId"));
				car.setCarId(res.getInt("carId"));
				car.setMyCarImg(res.getString("myCarImg"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return car;
	}

	@Override
	public int addMycar(Mycar mycar) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql="";
		if(mycar.getMyCarImg()==null) {
			sql = "INSERT INTO mycar(myCarName,myCarNum,myCarMileage,myCarDate,myCarMaint,userId,carId) VALUES (?,?,?,?,?,?,?)";
		}else {
			sql = "INSERT INTO mycar(myCarName,myCarNum,myCarMileage,myCarDate,myCarMaint,userId,carId,myCarImg) VALUES (?,?,?,?,?,?,?,?)";
		}
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, mycar.getMyCarName());
			ps.setString(2, mycar.getMyCarNum());
			ps.setString(3, mycar.getMyCarMileage());
			ps.setDate(4, new Date(mycar.getMyCarDate().getTime()));
			ps.setString(5, mycar.getMyCarMaint());
			ps.setInt(6, mycar.getUserId());
			ps.setInt(7, mycar.getCarId());
			if(mycar.getMyCarImg()!=null) {
				ps.setString(8, mycar.getMyCarImg());
			}
			System.out.println(ps);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

}
